CREATE PROCEDURE sp_asi_NameAddressNameGlobalSync
AS
/* WORK PHONE #################### */
DECLARE @WORK_PHONE_PURPOSE varchar(255)
SELECT @WORK_PHONE_PURPOSE = (
     SELECT ShortValue FROM System_Params
     WHERE ParameterName = 'Member_Control.' + (
         SELECT ShortValue FROM System_Params
         WHERE ParameterName = 'Member_Control.SyncWorkPhoneToAddress'
     ) + 'Prompt'
)
IF @WORK_PHONE_PURPOSE IS NOT NULL
BEGIN
     /* Name to Name_Address Synchronization - Parent Records */
     UPDATE Name_Address SET
         PHONE = n.WORK_PHONE,
         LAST_UPDATED = GETDATE()
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT n.WORK_PHONE = ''
     AND na.PHONE = ''
     AND na.PURPOSE = @WORK_PHONE_PURPOSE
     SELECT
         n.ID,
         n.WORK_PHONE AS VAL_BEFORE,
         na.PHONE AS VAL_AFTER
     INTO #tmp_SyncW
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT na.PHONE = ''
     AND NOT na.PHONE = n.WORK_PHONE
     AND na.PURPOSE = @WORK_PHONE_PURPOSE
     IF (SELECT COUNT(*) FROM #tmp_SyncW) > 0
     BEGIN
         CREATE UNIQUE INDEX ndx_SyncW_ID ON #tmp_SyncW (ID)
         /* Name_Address to Name Synchronization - Parent Records */
         UPDATE Name SET
             WORK_PHONE = ts.VAL_AFTER,
             LAST_UPDATED = GETDATE()
         FROM #tmp_SyncW ts
         WHERE Name.ID = ts.ID
         /* Change Log Inserts - Parent Records */
         INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
         SELECT
             GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
             ts.ID,
             'Name.WORK_PHONE: ' + ts.VAL_BEFORE + ' -> ' + ts.VAL_AFTER
         FROM #tmp_SyncW ts
         /* Meetings Flowdown - Parent Records */
         UPDATE Orders SET
             PHONE = ts.VAL_AFTER
         FROM #tmp_SyncW ts
         WHERE Orders.ST_ID = ts.ID
         AND Orders.SOURCE_SYSTEM = 'MEETING'
         AND Orders.PHONE = ts.VAL_BEFORE
         IF (
             SELECT COUNT(*) FROM System_Params
             WHERE ParameterName = 'Member_Control.DisableAutoFlowDown'
             AND ShortValue = 'YES'
         ) = 0
         BEGIN
             /* Name Parent to Child Flowdown Records */
             SELECT
                 n.ID,
                 ts.VAL_BEFORE,
                 ts.VAL_AFTER
             INTO #tmp_SyncChildrenW
             FROM #tmp_SyncW ts
             JOIN Name n ON n.CO_ID = ts.ID
             WHERE n.COMPANY_RECORD = 0
             AND n.WORK_PHONE = ts.VAL_BEFORE     
             IF (SELECT COUNT(*) FROM #tmp_SyncChildrenW) > 0
             BEGIN
                 /* Name_Address to Name Synchronization - Child Records */
                 UPDATE Name SET
                     WORK_PHONE = tsc.VAL_AFTER,
                     LAST_UPDATED = GETDATE()
                 FROM #tmp_SyncChildrenW tsc
                 WHERE Name.ID = tsc.ID
                 /* Change Log Inserts - Child Records */
                 INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
                 SELECT
                     GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
                     tsc.ID,
                     'Name.WORK_PHONE: ' + tsc.VAL_BEFORE + ' -> ' + tsc.VAL_AFTER
                 FROM #tmp_SyncChildrenW tsc
                 /* Name to Name_Address Synchronization - Child Records */
                 UPDATE Name_Address SET
                     PHONE = n.WORK_PHONE,
                     LAST_UPDATED = GETDATE()
                 FROM Name_Address na
                 JOIN Name n ON n.ID = na.ID
                 WHERE NOT n.WORK_PHONE = ''
                 AND na.PURPOSE = @WORK_PHONE_PURPOSE
             END
             DROP TABLE #tmp_SyncChildrenW
             /* Meetings Flowdown - Child Records */
             UPDATE Orders SET
                 PHONE = ts.VAL_AFTER
             FROM #tmp_SyncW ts
             WHERE Orders.CO_ID = ts.ID
             AND Orders.SOURCE_SYSTEM = 'MEETING'
             AND Orders.PHONE = ts.VAL_BEFORE
         END
     END
     DROP TABLE #tmp_SyncW
END
/* FAX #################### */
DECLARE @FAX_PURPOSE varchar(255)
SELECT @FAX_PURPOSE = (
     SELECT ShortValue FROM System_Params
     WHERE ParameterName = 'Member_Control.' + (
         SELECT ShortValue FROM System_Params
         WHERE ParameterName = 'Member_Control.SyncFaxToAddress'
     ) + 'Prompt'
)
IF @FAX_PURPOSE IS NOT NULL
BEGIN
     /* Name to Name_Address Synchronization - Parent Records */
     UPDATE Name_Address SET
         FAX = n.FAX,
         LAST_UPDATED = GETDATE()
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT n.FAX = ''
     AND na.FAX = ''
     AND na.PURPOSE = @FAX_PURPOSE
     SELECT
         n.ID,
         n.FAX AS VAL_BEFORE,
         na.FAX AS VAL_AFTER
     INTO #tmp_SyncF
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT na.FAX = ''
     AND NOT na.FAX = n.FAX
     AND na.PURPOSE = @FAX_PURPOSE
     IF (SELECT COUNT(*) FROM #tmp_SyncF) > 0
     BEGIN
         CREATE UNIQUE INDEX ndx_SyncF_ID ON #tmp_SyncF (ID)
         /* Name_Address to Name Synchronization - Parent Records */
         UPDATE Name SET
             FAX = ts.VAL_AFTER,
             LAST_UPDATED = GETDATE()
         FROM #tmp_SyncF ts
         WHERE Name.ID = ts.ID
         /* Change Log Inserts - Parent Records */
         INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
         SELECT
             GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
             ts.ID,
             'Name.FAX: ' + ts.VAL_BEFORE + ' -> ' + ts.VAL_AFTER
         FROM #tmp_SyncF ts
         /* Meetings Flowdown - Parent Records */
         UPDATE Orders SET
             FAX = ts.VAL_AFTER
         FROM #tmp_SyncF ts
         WHERE Orders.ST_ID = ts.ID
         AND Orders.SOURCE_SYSTEM = 'MEETING'
         AND Orders.FAX = ts.VAL_BEFORE
         IF (
             SELECT COUNT(*) FROM System_Params
             WHERE ParameterName = 'Member_Control.DisableAutoFlowDown'
             AND ShortValue = 'YES'
         ) = 0
         BEGIN
             /* Name Parent to Child Flowdown Records */
             SELECT
                 n.ID,
                 ts.VAL_BEFORE,
                 ts.VAL_AFTER
             INTO #tmp_SyncChildrenF
             FROM #tmp_SyncF ts
             JOIN Name n ON n.CO_ID = ts.ID
             WHERE n.COMPANY_RECORD = 0
             AND n.FAX = ts.VAL_BEFORE     
             IF (SELECT COUNT(*) FROM #tmp_SyncChildrenF) > 0
             BEGIN
                 /* Name_Address to Name Synchronization - Child Records */
                 UPDATE Name SET
                     FAX = tsc.VAL_AFTER,
                     LAST_UPDATED = GETDATE()
                 FROM #tmp_SyncChildrenF tsc
                 WHERE Name.ID = tsc.ID
                 /* Change Log Inserts - Child Records */
                 INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
                 SELECT
                     GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
                     tsc.ID,
                     'Name.FAX: ' + tsc.VAL_BEFORE + ' -> ' + tsc.VAL_AFTER
                 FROM #tmp_SyncChildrenF tsc
                 /* Name to Name_Address Synchronization - Child Records */
                 UPDATE Name_Address SET
                     FAX = n.FAX,
                     LAST_UPDATED = GETDATE()
                 FROM Name_Address na
                 JOIN Name n ON n.ID = na.ID
                 WHERE NOT n.FAX = ''
                 AND na.PURPOSE = @FAX_PURPOSE
             END
             DROP TABLE #tmp_SyncChildrenF
             /* Meetings Flowdown - Child Records */
             UPDATE Orders SET
                 FAX = ts.VAL_AFTER
             FROM #tmp_SyncF ts
             WHERE Orders.CO_ID = ts.ID
             AND Orders.SOURCE_SYSTEM = 'MEETING'
             AND Orders.FAX = ts.VAL_BEFORE
         END
     END
     DROP TABLE #tmp_SyncF
END
/* EMAIL #################### */
DECLARE @EMAIL_PURPOSE varchar(255)
SELECT @EMAIL_PURPOSE = (
     SELECT ShortValue FROM System_Params
     WHERE ParameterName = 'Member_Control.' + (
         SELECT ShortValue FROM System_Params
         WHERE ParameterName = 'Member_Control.SyncEmailToAddress'
     ) + 'Prompt'
)
IF @EMAIL_PURPOSE IS NOT NULL
BEGIN
     /* Name to Name_Address Synchronization - Parent Records */
     UPDATE Name_Address SET
         EMAIL = n.EMAIL,
         LAST_UPDATED = GETDATE()
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT n.EMAIL = ''
     AND na.EMAIL = ''
     AND na.PURPOSE = @EMAIL_PURPOSE
     SELECT
         n.ID,
         n.EMAIL AS VAL_BEFORE,
         na.EMAIL AS VAL_AFTER
     INTO #tmp_SyncE
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT na.EMAIL = ''
     AND NOT na.EMAIL = n.EMAIL
     AND na.PURPOSE = @EMAIL_PURPOSE
     IF (SELECT COUNT(*) FROM #tmp_SyncE) > 0
     BEGIN
         CREATE UNIQUE INDEX ndx_SyncE_ID ON #tmp_SyncE (ID)
         /* Name_Address to Name Synchronization - Parent Records */
         UPDATE Name SET
             EMAIL = ts.VAL_AFTER,
             LAST_UPDATED = GETDATE()
         FROM #tmp_SyncE ts
         WHERE Name.ID = ts.ID
         /* Change Log Inserts - Parent Records */
         INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
         SELECT
             GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
             ts.ID,
             'Name.EMAIL: ' + ts.VAL_BEFORE + ' -> ' + ts.VAL_AFTER
         FROM #tmp_SyncE ts
         /* Meetings Flowdown - Parent Records */
         UPDATE Orders SET
             EMAIL = ts.VAL_AFTER
         FROM #tmp_SyncE ts
         WHERE Orders.ST_ID = ts.ID
         AND Orders.SOURCE_SYSTEM = 'MEETING'
         AND Orders.EMAIL = ts.VAL_BEFORE
         /* Email no longer honored as part of user-defined flowdown */
         /*
         IF (
             SELECT COUNT(*) FROM System_Params
             WHERE ParameterName = 'Member_Control.FlowDownFields'
             AND (ShortValue LIKE '%Name.EMAIL%' OR LongValue LIKE '%Name.EMAIL%')
         ) = 1
         BEGIN
             SELECT
                 n.ID,
                 ts.VAL_BEFORE,
                 ts.VAL_AFTER
             INTO #tmp_SyncChildrenE
             FROM #tmp_SyncE ts
             JOIN Name n ON n.CO_ID = ts.ID
             WHERE n.COMPANY_RECORD = 0
             AND n.EMAIL = ts.VAL_BEFORE     
             IF (SELECT COUNT(*) FROM #tmp_SyncChildrenE) > 0
             BEGIN
                 UPDATE Name SET
                     EMAIL = tsc.VAL_AFTER,
                     LAST_UPDATED = GETDATE()
                 FROM #tmp_SyncChildrenE tsc
                 WHERE Name.ID = tsc.ID
                 INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
                 SELECT
                     GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
                     tsc.ID,
                     'Name.EMAIL: ' + tsc.VAL_BEFORE + ' -> ' + tsc.VAL_AFTER
                 FROM #tmp_SyncChildrenE tsc
                 UPDATE Name_Address SET
                     EMAIL = n.EMAIL,
                     LAST_UPDATED = GETDATE()
                 FROM Name_Address na
                 JOIN Name n ON n.ID = na.ID
                 WHERE NOT n.EMAIL = ''
                 AND na.PURPOSE = @EMAIL_PURPOSE
             END
             DROP TABLE #tmp_SyncChildrenE
             UPDATE Orders SET
                 EMAIL = ts.VAL_AFTER
             FROM #tmp_SyncE ts
             WHERE Orders.CO_ID = ts.ID
             AND Orders.SOURCE_SYSTEM = 'MEETING'
             AND Orders.EMAIL = ts.VAL_BEFORE
         END
         */
     END
     DROP TABLE #tmp_SyncE
END
/* HOME PHONE #################### */
DECLARE @HOME_PHONE_PURPOSE varchar(255)
SELECT @HOME_PHONE_PURPOSE = (
     SELECT ShortValue FROM System_Params
     WHERE ParameterName = 'Member_Control.' + (
         SELECT ShortValue FROM System_Params
         WHERE ParameterName = 'Member_Control.SyncHomePhoneToAddress'
     ) + 'Prompt'
)
IF @HOME_PHONE_PURPOSE IS NOT NULL
BEGIN
     /* Name to Name_Address Synchronization - Parent Records */
     UPDATE Name_Address SET
         PHONE = n.HOME_PHONE,
         LAST_UPDATED = GETDATE()
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT n.HOME_PHONE = ''
     AND na.PHONE = ''
     AND na.PURPOSE = @HOME_PHONE_PURPOSE
     SELECT
         n.ID,
         n.HOME_PHONE AS VAL_BEFORE,
         na.PHONE AS VAL_AFTER
     INTO #tmp_SyncH
     FROM Name_Address na
     JOIN Name n ON n.ID = na.ID
     WHERE NOT na.PHONE = ''
     AND NOT na.PHONE = n.HOME_PHONE
     AND na.PURPOSE = @HOME_PHONE_PURPOSE
     IF (SELECT COUNT(*) FROM #tmp_SyncH) > 0
     BEGIN
         CREATE UNIQUE INDEX ndx_SyncH_ID ON #tmp_SyncH (ID)
         UPDATE Name SET
             HOME_PHONE = ts.VAL_AFTER,
             LAST_UPDATED = GETDATE()
         FROM #tmp_SyncH ts
         WHERE Name.ID = ts.ID
         /* Change Log Inserts - Parent Records */
         INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
         SELECT
             GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
             ts.ID,
             'Name.HOME_PHONE: ' + ts.VAL_BEFORE + ' -> ' + ts.VAL_AFTER
         FROM #tmp_SyncH ts
         /* Meetings Flowdown - Parent Records */
         /* N/A (Home phone not part of orders/meetings) */
         /* Home phone no longer honored as part of user-defined flowdown */
         /*
         IF (
             SELECT COUNT(*) FROM System_Params
             WHERE ParameterName = 'Member_Control.FlowDownFields'
             AND (ShortValue LIKE '%Name.HOME_PHONE%' OR LongValue LIKE '%Name.HOME_PHONE%')
         ) = 1
         BEGIN
             SELECT
                 n.ID,
                 ts.VAL_BEFORE,
                 ts.VAL_AFTER
             INTO #tmp_SyncChildrenH
             FROM #tmp_SyncH ts
             JOIN Name n ON n.CO_ID = ts.ID
             WHERE n.COMPANY_RECORD = 0
             AND n.HOME_PHONE = ts.VAL_BEFORE     
             IF (SELECT COUNT(*) FROM #tmp_SyncChildrenH) > 0
             BEGIN
                 UPDATE Name SET
                     HOME_PHONE = tsc.VAL_AFTER,
                     LAST_UPDATED = GETDATE()
                 FROM #tmp_SyncChildrenH tsc
                 WHERE Name.ID = tsc.ID
                 INSERT INTO Name_Log (DATE_TIME, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
                 SELECT
                     GETDATE(), 'CHANGE', 'CHANGE', 'SYNC',
                     tsc.ID,
                     'Name.HOME_PHONE: ' + tsc.VAL_BEFORE + ' -> ' + tsc.VAL_AFTER
                 FROM #tmp_SyncChildrenH tsc
                 UPDATE Name_Address SET
                     PHONE = n.HOME_PHONE,
                     LAST_UPDATED = GETDATE()
                 FROM Name_Address na
                 JOIN Name n ON n.ID = na.ID
                 WHERE NOT n.HOME_PHONE = ''
                 AND na.PURPOSE = @HOME_PHONE_PURPOSE
             END
             DROP TABLE #tmp_SyncChildrenH
         END
         */
     END
     DROP TABLE #tmp_SyncH
END

GO
GRANT EXECUTE ON  [dbo].[sp_asi_NameAddressNameGlobalSync] TO [IMIS]
GO